﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAOrderDetail
    {

        public static Boolean Delete(OrderDetail orderDetail, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " DELETE orderdetail " +
                              " WHERE ID = " + orderDetail.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(OrderDetail orderDetail, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE orderdetail SET " +
                              "  OrderID = " + ((string.IsNullOrEmpty(orderDetail.OrderMaster.ID.ToString()) || (orderDetail.OrderMaster.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.OrderMaster.ID.ToString()) +
                              " ,ProductID = " + ((string.IsNullOrEmpty(orderDetail.Product.ID.ToString()) || (orderDetail.Product.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Product.ID.ToString()) +
                              " ,ShapeID = " + ((string.IsNullOrEmpty(orderDetail.Shape.ID.ToString()) || (orderDetail.Shape.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Shape.ID.ToString()) +
                              " ,Cnt = " + ((string.IsNullOrEmpty(orderDetail.Cnt.ToString()) || (orderDetail.Cnt == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Cnt.ToString()) +
                              " ,Price = " + ((string.IsNullOrEmpty(orderDetail.Price.ToString()) || (orderDetail.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Price.ToString().Replace(',', '.')) +
                              " WHERE ID = " + orderDetail.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(OrderDetail orderDetail, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO orderdetail (OrderID, ProductID, ShapeID, Cnt, Price) " +
                              " VALUES( " + ((string.IsNullOrEmpty(orderDetail.OrderMaster.ID.ToString()) || (orderDetail.OrderMaster.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.OrderMaster.ID.ToString()) +
                              " , " + ((string.IsNullOrEmpty(orderDetail.Product.ID.ToString()) || (orderDetail.Product.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Product.ID.ToString()) +
                              " ," + ((orderDetail.Shape == null || string.IsNullOrEmpty(orderDetail.Shape.ID.ToString()) || (orderDetail.Shape.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Shape.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(orderDetail.Cnt.ToString()) || (orderDetail.Cnt == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Cnt.ToString()) +
                              " ," + ((string.IsNullOrEmpty(orderDetail.Price.ToString()) || (orderDetail.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetail.Price.ToString().Replace(',', '.')) +
                              ")";

            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                orderDetail.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<OrderDetail> Select(OrderDetail orderDetail)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT od.* " +
            " FROM orderdetail od " +
            " WHERE ((od.ID = " + orderDetail.ID.ToString() + ") OR (" + orderDetail.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "ORDER BY od.ID";

            SqlCommand cmd = new SqlCommand(_cmdText, con);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(_cmdText, con);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<OrderDetail> _orderDetailList = new List<OrderDetail>();

            foreach (DataRow row in tbl.Rows)
            {
                OrderDetail _orderDetail = new OrderDetail();

                _orderDetail.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _orderDetail.OrderMaster = new OrderMaster();
                _orderDetail.OrderMaster.ID = (row["OrderID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["OrderID"]));

                _orderDetail.Product = new Product();
                _orderDetail.Product.ID = (row["ProductID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ProductID"]));

                _orderDetail.Shape = new Shape();
                _orderDetail.Shape.ID = (row["ShapeID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ShapeID"]));
                
                _orderDetail.Cnt = (row["Cnt"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["Cnt"]));
                _orderDetail.Price = (row["Price"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price"]));

                _orderDetailList.Add(_orderDetail);
            }

            con.Close();
            return _orderDetailList;
        }
    }
}